import { PrismaClient } from '../lib/generated/prisma'
import type { User, Project, TodoList, Audit } from '../lib/generated/prisma'

// 使用单例模式，避免创建多个Prisma客户端实例
const globalForPrisma = globalThis as unknown as {
  prisma: PrismaClient | undefined
}

const prisma = globalForPrisma.prisma ?? new PrismaClient({
  log: ['error', 'warn'],
  datasources: {
    db: {
      url: process.env.DATABASE_URL + "?connection_limit=1&pool_timeout=20"
    }
  },
  transactionOptions: {
    timeout: 30000, // 增加事务超时时间到30秒
  },
})

if (process.env.NODE_ENV !== 'production') globalForPrisma.prisma = prisma

// 初始化数据库（Prisma中不需要手动创建表）
export async function initDatabase() {
  console.log('Prisma数据库连接已建立')
  return true
}

// 用户相关操作
export const userQueries = {
  // 根据ID查找用户
  findById: {
    get: async (id: number): Promise<User | null> => {
      return await prisma.user.findUnique({
        where: { id }
      })
    }
  },
  
  // 根据Casdoor ID查找用户
  findByCasdoorId: {
    get: async (casdoorId: string): Promise<User | null> => {
      return await prisma.user.findUnique({
        where: { casdoorId }
      })
    }
  },
  
  // 根据用户名查找用户
  findByName: {
    get: async (name: string): Promise<User | null> => {
      return await prisma.user.findUnique({
        where: { name }
      })
    }
  },
  
  // 创建新用户
  create: {
    run: async (name: string, color: string, casdoorId: string, email: string, avatar: string) => {
      const result = await prisma.user.create({
        data: {
          name,
          color,
          casdoorId,
          email,
          avatar
        }
      })
      return { lastInsertRowid: result.id }
    }
  },
  
  // 更新用户信息
  update: {
    run: async (name: string, email: string, avatar: string, casdoorId: string) => {
      await prisma.user.update({
        where: { casdoorId },
        data: {
          name,
          email,
          avatar
        }
      })
    }
  },
  
  // 更新用户的Casdoor ID
  updateCasdoorId: {
    run: async (casdoorId: string, id: number) => {
      await prisma.user.update({
        where: { id },
        data: { casdoorId }
      })
    }
  },
  
  // 获取所有用户
  getAll: {
    all: async (): Promise<User[]> => {
      return await prisma.user.findMany({
        orderBy: { createdAt: 'desc' }
      })
    }
  },
  
  // 更新用户颜色
  updateColor: {
    run: async (color: string, id: number) => {
      await prisma.user.update({
        where: { id },
        data: { color }
      })
    }
  }
}

// Todolist相关操作
export const todoQueries = {
  // 创建新todo
  create: {
    run: async (content: string, createdBy: number, tags: string | null, status: string, assignedTo: number | null) => {
      const result = await prisma.todoList.create({
        data: {
          content,
          createdBy,
          tags,
          status,
          assignedTo
        }
      })
      return { lastInsertRowid: result.id }
    }
  },
  
  // 根据ID查找todo
  findById: {
    get: async (id: number) => {
      return await prisma.todoList.findUnique({
        where: { id }
      })
    }
  },
  
  // 获取所有未归档的todos
  getAllActive: {
    all: async () => {
      return await prisma.todoList.findMany({
        where: { archived: false },
        include: {
          creator: true,
          assignee: true
        },
        orderBy: { createdAt: 'desc' }
      })
    }
  },
  
  // 根据项目ID获取所有关联的todos的完整信息
  getAllByProjectId: {
    all: async (projectId: number) => {
      const todos = await prisma.todoList.findMany({
        where: {
          archived: false,
          todoProjectLinks: {
            some: {
              projectId
            }
          }
        },
        include: {
          creator: true,
          assignee: true
        },
        orderBy: { createdAt: 'desc' }
      })
      
      // 获取所有已完成todo的完成时间
      const completedTodoIds = todos.filter(todo => todo.status === '已完成').map(todo => todo.id)
      const completionTimes: Record<number, Date> = {}
      
      if (completedTodoIds.length > 0) {
        const completionAudits = await prisma.audit.findMany({
          where: {
            todolistId: { in: completedTodoIds },
            action: {
              contains: '"act":"update"'
            }
          },
          orderBy: { createdAt: 'desc' }
        })
        
        // 解析每个audit记录，找到状态更新为"已完成"的时间
        for (const audit of completionAudits) {
          try {
            const actionData = JSON.parse(audit.action)
            if (actionData.act === 'update' && 
                actionData.newValue && 
                actionData.newValue.status === '已完成' &&
                audit.todolistId) {
              // 如果还没有记录这个todo的完成时间，或者这个audit记录更晚，则更新
              if (!completionTimes[audit.todolistId] || 
                  audit.createdAt > completionTimes[audit.todolistId]) {
                completionTimes[audit.todolistId] = audit.createdAt
              }
            }
          } catch (error) {
            console.error('解析audit action失败:', error)
          }
        }
      }
      
      // 转换格式以匹配现有的API，并添加用时信息
      return todos.map(todo => {
        let completionTime = null
        let timeSpent = null
        
        if (todo.status === '已完成' && completionTimes[todo.id]) {
          completionTime = completionTimes[todo.id]
          const timeDiff = completionTime.getTime() - todo.createdAt.getTime()
          timeSpent = timeDiff / (1000 * 60 * 60 * 24) // 转换为天数
        }
        
        return {
          todolist_id: todo.id,
          content: todo.content,
          created_by: todo.createdBy,
          tags: todo.tags,
          status: todo.status,
          assigned_to: todo.assignedTo,
          archived: todo.archived,
          created_at: todo.createdAt,
          creator_name: todo.creator.name,
          creator_color: todo.creator.color,
          assignee_name: todo.assignee?.name,
          assignee_color: todo.assignee?.color,
          completion_time: completionTime,
          time_spent: timeSpent // 用时（天数）
        }
      })
    }
  },
  
  // 根据状态获取todos
  getByStatus: {
    all: async (status: string) => {
      return await prisma.todoList.findMany({
        where: { 
          status,
          archived: false 
        },
        include: {
          creator: true,
          assignee: true
        },
        orderBy: { createdAt: 'desc' }
      })
    }
  },
  
  // 更新todo状态
  updateStatus: {
    run: async (status: string, id: number) => {
      await prisma.todoList.update({
        where: { id },
        data: { status }
      })
    }
  },
  
  // 更新todo内容
  update: {
    run: async (content: string, tags: string | null, assignedTo: number | null, status: string, id: number) => {
      await prisma.todoList.update({
        where: { id },
        data: {
          content,
          tags,
          assignedTo,
          status
        }
      })
    }
  },
  
  // 归档todo
  archive: {
    run: async (id: number) => {
      await prisma.todoList.update({
        where: { id },
        data: { archived: true }
      })
    }
  },
  
  // 删除todo
  delete: {
    run: async (id: number) => {
      await prisma.todoList.delete({
        where: { id }
      })
    }
  },
  
  // 自动归档已完成超过7天的任务
  autoArchive: {
    run: async () => {
      const sevenDaysAgo = new Date()
      sevenDaysAgo.setDate(sevenDaysAgo.getDate() - 7)
      
      const result = await prisma.todoList.updateMany({
        where: {
          status: '已完成',
          archived: false,
          createdAt: {
            lt: sevenDaysAgo
          }
        },
        data: { archived: true }
      })
      return { changes: result.count }
    }
  },
  
  // 根据用户ID获取分配给该用户的todos
  getByAssignedUser: {
    all: async (assignedTo: number) => {
      return await prisma.todoList.findMany({
        where: { 
          assignedTo,
          archived: false 
        },
        include: {
          creator: true
        },
        orderBy: { createdAt: 'desc' }
      })
    }
  },
  
  // 根据创建者ID获取todos
  getByCreator: {
    all: async (createdBy: number) => {
      return await prisma.todoList.findMany({
        where: { 
          createdBy,
          archived: false 
        },
        include: {
          assignee: true
        },
        orderBy: { createdAt: 'desc' }
      })
    }
  },
  
  // 删除项目关联的所有待办事项
  deleteByProjectId: {
    run: async (projectId: number) => {
      // 首先获取所有关联的todolist IDs
      const todoProjectLinks = await prisma.todoProjectLink.findMany({
        where: { projectId },
        select: { todolistId: true }
      })
      
      const todoIds = todoProjectLinks.map(link => link.todolistId)
      
      if (todoIds.length > 0) {
        // 删除TodoList记录
        await prisma.todoList.deleteMany({
          where: {
            id: { in: todoIds }
          }
        })
      }
    }
  }
}

// 项目相关操作
export const projectQueries = {
  // 创建新项目
  create: {
    run: async (name: string, description: string) => {
      const result = await prisma.project.create({
        data: {
          name,
          description
        }
      })
      return { lastInsertRowid: result.id }
    }
  },
  
  // 根据ID查找项目
  findById: {
    get: async (id: number): Promise<Project | null> => {
      return await prisma.project.findUnique({
        where: { id }
      })
    }
  },
  
  // 获取所有项目
  getAll: {
    all: async (): Promise<Project[]> => {
      return await prisma.project.findMany({
        orderBy: { createdAt: 'desc' }
      })
    }
  },
  
  // 更新项目描述
  update: {
    run: async (description: string, id: number) => {
      await prisma.project.update({
        where: { id },
        data: { description }
      })
    }
  },
  
  // 更新项目名称
  updateName: {
    run: async (name: string, id: number) => {
      await prisma.project.update({
        where: { id },
        data: { name }
      })
    }
  },
  
  // 删除项目
  delete: {
    run: async (id: number) => {
      await prisma.project.delete({
        where: { id }
      })
    }
  },
  
  // 更新项目归档状态
  updateArchiveStatus: {
    run: async (isArchived: boolean, id: number) => {
      await prisma.project.update({
        where: { id },
        data: { isArchived }
      })
    }
  },
  
  // 获取用户参与的项目
  getByUserId: {
    all: async (userId: number) => {
      const userProjectLinks = await prisma.userProjectLink.findMany({
        where: { userId },
        include: {
          project: true
        },
        orderBy: {
          project: {
            createdAt: 'desc'
          }
        }
      })
      
      return userProjectLinks.map(link => link.project)
    }
  }
}

// Todo与项目关联操作
export const todoProjectLinkQueries = {
  // 创建关联
  create: {
    run: async (todolistId: number, projectId: number) => {
      await prisma.todoProjectLink.create({
        data: {
          todolistId,
          projectId
        }
      })
    }
  },
  
  // 解除关联
  unlink: {
    run: async (todolistId: number, projectId: number) => {
      await prisma.todoProjectLink.deleteMany({
        where: {
          todolistId,
          projectId
        }
      })
    }
  },
  
  // 根据项目ID获取所有关联的todo ID
  getTodosByProjectId: {
    all: async (projectId: number) => {
      const links = await prisma.todoProjectLink.findMany({
        where: { projectId },
        select: { todolistId: true }
      })
      return links.map(link => ({ todolist_id: link.todolistId }))
    }
  },
  
  // 根据todo ID获取所有关联的项目ID
  getProjectsByTodoId: {
    all: async (todolistId: number) => {
      const links = await prisma.todoProjectLink.findMany({
        where: { todolistId },
        select: { projectId: true }
      })
      return links.map(link => ({ project_id: link.projectId }))
    }
  }
}

// 用户与项目关联操作
export const userProjectLinkQueries = {
  // 创建关联
  create: {
    run: async (userId: number, projectId: number) => {
      await prisma.userProjectLink.create({
        data: {
          userId,
          projectId
        }
      })
    }
  },
  
  // 解除关联
  unlink: {
    run: async (userId: number, projectId: number) => {
      await prisma.userProjectLink.deleteMany({
        where: {
          userId,
          projectId
        }
      })
    }
  },
  
  // 根据项目ID获取所有关联的用户完整信息
  getUsersByProjectId: {
    all: async (projectId: number) => {
      const userProjectLinks = await prisma.userProjectLink.findMany({
        where: { projectId },
        include: {
          user: true
        },
        orderBy: {
          user: {
            createdAt: 'asc'
          }
        }
      })
      
      return userProjectLinks.map(link => link.user)
    }
  },
  
  // 根据用户ID获取所有关联的项目ID
  getProjectsByUserId: {
    all: async (userId: number) => {
      const links = await prisma.userProjectLink.findMany({
        where: { userId },
        select: { projectId: true }
      })
      return links.map(link => ({ project_id: link.projectId }))
    }
  },
  
  // 检查用户是否参与项目
  checkUserInProject: {
    get: async (userId: number, projectId: number) => {
      const count = await prisma.userProjectLink.count({
        where: {
          userId,
          projectId
        }
      })
      return { count }
    }
  },
  
  deleteByProjectId: {
    run: async (projectId: number) => {
      await prisma.userProjectLink.deleteMany({
        where: { projectId }
      })
    }
  }
}

// 审计日志相关操作
export const auditQueries = {
  // 创建审计日志
  create: {
    run: async (projectId: number | null, todolistId: number | null, userId: number, action: string) => {
      await prisma.audit.create({
        data: {
          projectId,
          todolistId,
          userId,
          action
        }
      })
    }
  },
  
  // 根据项目ID获取审计日志
  getByProjectId: {
    all: async (projectId: number) => {
      return await prisma.audit.findMany({
        where: { projectId },
        orderBy: { createdAt: 'desc' }
      })
    }
  },
  
  // 根据用户ID获取审计日志
  getByUserId: {
    all: async (userId: number) => {
      return await prisma.audit.findMany({
        where: { userId },
        orderBy: { createdAt: 'desc' }
      })
    }
  }
}

/**
 * 创建审计日志记录
 * @param userId 用户ID
 * @param action 操作详情，JSON字符串
 * @param projectId 项目ID (可选)
 * @param todolistId Todolist ID (可选)
 */
export async function createAuditLog(
  userId: number,
  action: object,
  projectId?: number,
  todolistId?: number
) {
  try {
    const actionString = JSON.stringify(action)
    await auditQueries.create.run(projectId || null, todolistId || null, userId, actionString)
    console.log('审计日志已记录:', { userId, projectId, todolistId, action })
  } catch (error) {
    console.error('记录审计日志失败:', error)
    // 根据实际需求，这里可以抛出错误或进行其他错误处理
  }
}

// 导出Prisma实例，以便在其他地方使用
export default prisma 